from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from queue import Queue
import openpyxl.workbook
from config import *
from sql import sqlseverDB
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
import tkinter.messagebox as messagebox
import openpyxl,time,os,concurrent.futures


class ALLegroFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='allegro功能', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)   
        ProGroup(self).pack(fill=X,padx=3,pady=3)
class ProGroup(Frame):
    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.pack(fill=X,pady=2,padx=2)
        self.createWidgets()
        Thread(target=self.getmbData).start()
        
    def createWidgets(self):
        
        self.mbStr=StringVar()
        self.mbStr.set('正在allegro分组产品数据...')
        f2=Frame(self)
        f2.pack(fill=X,padx=2,pady=2)
        Label(f2, textvariable=self.mbStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        Thread(target=self.getmbcount).start()


        self.firstjz=True
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        columns = ("gid","groupName","dpcount","tjpcount","hypcount","state")
        tree = ttk.Treeview(f0, show = "headings", columns = columns)
        tree.column("gid", anchor = "center",width=50)
        tree.column("groupName", anchor = "center",width=150)
        tree.column("dpcount", anchor = "center",width=100)
        tree.column("tjpcount", anchor = "center",width=100)
        tree.column("hypcount", anchor = "center",width=100)
        tree.column("state", anchor = "center",width=330)


        tree.heading("gid", text = "序列号")
        tree.heading("groupName", text = "分组名字")
        tree.heading("dpcount", text = "产品总数量")
        tree.heading("tjpcount", text = "已审核体积")
        tree.heading("hypcount", text = "已找货源")
        tree.heading("state", text = "分组使用状态")
        
        self.VScroll1 = Scrollbar(f0, orient='vertical', command=tree.yview)
        self.VScroll1.pack(side=RIGHT,fill=Y)
        tree.configure(yscrollcommand=self.VScroll1.set)
        tree.pack(padx=3)
        self.tree=tree
        

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)

        self.btn_catmb=Button(f1,text='刷新',command=lambda:Thread(target=self.upmb).start(),fg='#ffffff',bg='green',width=20)
        self.btn_catmb.pack(side=LEFT, padx=2, pady=2)

        f2=Frame(self)
        f2.pack(fill=X,padx=2,pady=2)

        self.lab_a=StringVar(value='A=有货源+出单,数量:正在获取...')
        self.lab_b=StringVar(value='B=有货源+未出单,数量:正在获取...')
        self.lab_c=StringVar(value='C=体积审核通过+未查找货源,正在获取...')
        self.lab_n=StringVar(value='N=新采集,正在获取...')
        Label(f2, textvariable=self.lab_a,fg='#6F4242',anchor="w").pack(fill=X, padx=2)
        Label(f2, textvariable=self.lab_b,fg='#6F4242',anchor="w").pack(fill=X, padx=2)
        Label(f2, textvariable=self.lab_c,fg='#6F4242',anchor="w").pack( fill=X,padx=2)
        Label(f2, textvariable=self.lab_n,fg='#6F4242',anchor="w").pack(fill=X, padx=2)

    def getmbcount(self):
 
        while True:
            try:
                rs0=self.ms.ExecQuery('select count(*) from ALLGoodPro')
                rs1=self.ms.ExecQuery('select count(*) from ALLGoodPro where pw=1')
                rs2=self.ms.ExecQuery('select count(*) from ALLGoodPro where pw=1 and sold>0 and Y_1688url is not null')
                rs_a=self.ms.ExecQuery("select count(*) from ALLGoodPro where groupname like 'A%' ")
                self.lab_a.set(f'A=有货源+出单,数量:{rs_a[0][0]}')
                rs3=self.ms.ExecQuery('select count(*) from ALLGoodPro where pw=1 and (sold=0 or sold is null)  and Y_1688url is not null')
                rs_b=self.ms.ExecQuery("select count(*) from ALLGoodPro where groupname like 'B%' ")
                self.lab_b.set(f'B=有货源+未出单,数量:{rs_b[0][0]}')
                rs4=self.ms.ExecQuery('select count(*) from ALLGoodPro where pw=1 and (sold=0 or sold is null)  and shenhetiji=1 and Y_1688url is null')
                rs_c=self.ms.ExecQuery("select count(*) from ALLGoodPro where groupname like 'C%' ")
                self.lab_c.set(f'C=体积审核通过+未查找货源,数量:{rs_c[0][0]}')
                rs_n=self.ms.ExecQuery("select count(*) from ALLGoodPro where groupname like 'N%' ")
                self.lab_n.set(f'N=新采集,数量:{rs_n[0][0]}')
                self.mbStr.set(f'allegro 共有 {rs0[0][0]} 个产品,其中白名单 {rs1[0][0]} 个,实际A组{rs2[0][0]}个,实际B组 {rs3[0][0]} 个,实际C组: {rs4[0][0]} 个')

                
            except Exception as e:
                print(e)
            time.sleep(120)

    def update_group(self):
        ms=self.ms
        group_gm=[('A',20,"groupname not like 'A%' and pw=1 and sold>0 and Y_1688url is not null order by leimu"),
        ('B',200,"(groupname not like 'A%' and groupname not like 'B%') and pw=1 and (sold=0 or sold is null)  and Y_1688url is not null order by leimu"),
        ('C',2000,"(groupname not like 'A%' and groupname not like 'B%' and groupname not like 'C%') and pw=1 and (sold=0 or sold is null)  and shenhetiji=1 order by leimu")
        ]

        print(f'开始调整分组变动...')
        
        for ys_ganme,gcount,wstr in group_gm:
            rs=ms.ExecQuery(f'select pid from allgoodpro where {wstr}')
            print(f'共有 {len(rs)} 个 {ys_ganme} 分组的新数据...')
            suc=0
            if len(rs)>0:
                start_ind=0
                while True:
                    sqlstr_group=f'''select groupname,pcount from (select groupname,COUNT(*) AS pcount FROM ALLGoodPro 
                        where groupname like '{ys_ganme}%'
                        GROUP BY groupname) as p order by p.pcount'''
                    rs_group=ms.ExecQuery(sqlstr_group)
                    latest_group_name,latest_pcount= rs_group[0]
                    if latest_pcount>=gcount:
                        latest_group_name=f'{ys_ganme}{len(rs_group)+1}'
                        end_ind=(start_ind+1)*gcount 
                        if end_ind>len(rs):
                            end_ind=len(rs)
                        cur_pids=[str(r[0]) for r in rs[start_ind:end_ind]]


                    else:
                        end_ind=start_ind+gcount-latest_pcount
                        if end_ind>len(rs):
                            end_ind=len(rs)
                        cur_pids=[str(r[0]) for r in rs[start_ind:end_ind]]
                    
                    if not cur_pids:
                        break
                    pidstr=','.join(cur_pids)
                    #print(f"update ALLGoodPro set groupname=? where pid in ({pidstr})")
                    aff=ms.ExecNoQuery(f'update ALLGoodPro set groupname=? where pid in ({pidstr})',(latest_group_name,))
                    print(f'分组《{latest_group_name}》新增{aff}个数据')
                    suc+=aff
                    start_ind=end_ind
                
                print(f'分组《{ys_ganme}》一共新增 {suc} 个 数据')

    def upmb(self):
        self.btn_catmb.config(state=DISABLED)
        self.update_group()
        aff=self.ms.call_procedure('UpdateAlGroupCounts')
        print(f'更新分组数据状态:{aff}')
        self.getmbData()
        self.btn_catmb.config(state=NORMAL)
    
    def getmbData(self):
        time.sleep(3)
        if not self.firstjz:
            for _ in map(self.tree.delete, self.tree.get_children("")):
                pass
        else:
            self.firstjz=False

        sqlstr=f'''select id,gname,gcount,tjcount,gwdcount,state from algroup order by 
                     LEFT(gname, PATINDEX('%[0-9]%', gname + '0') - 1), 
                    TRY_CAST(SUBSTRING(gname, PATINDEX('%[0-9]%', gname + '0'), LEN(gname)) AS INT);'''
        
        rs=ms.ExecQuery(sqlstr)
        rs2=ms.ExecQuery('select dpname,DataGroup from houtai_alg where isupload=1')
        group_dict={}
        for dpname,gnamestr in rs2:
            gnames=gnamestr.split('|')
            for gname in gnames:
                if group_dict.get(gname):
                    group_dict[gname].append(dpname)
                else:
                    group_dict[gname]=[dpname]

        i=1
        for v in rs:
            cv=list(v)
            cv[0]=i
            dplist=group_dict.get(cv[1])
            if dplist:
                if len(dplist)==1:
                    cv[-1]=dplist[0]
                elif len(dplist)<=4:
                    cv[-1]=','.join([sss[:10] for sss in dplist])
                else:
                    cv[-1]=f"{','.join([sss[:10] for sss in dplist[:3]])} 等{len(dplist)}个店铺"
            else:
                cv[-1]='未使用'
            cvalues=cv
            self.tree.insert('', i, values =cvalues)
            i += 1

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("860x700")
# list_win.title('allgero')
# ALLegroFra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()